package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sample.action.form.AreaForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 定义用户模型，封装用户属性及方法
 * @author 演示
 *
 */
public class Area {

	private static Log LOG =LogFactory.getLog(Area.class);
	
	private Integer id;
	private String cityno;
	private String handleno;
	private String agenName;
	private String type;
	// 实例化JdbcUtils组件，完成加载驱动以及连接数据库操作，获得连接实例
	private JdbcUtils jdbcUtils = new JdbcUtils();
	
	
	
	public Area(Integer id){
		this.id =id;
	}
	/**
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_area where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.id =rs.getInt("id");
				this.cityno =rs.getString("cityno");
				this.handleno =rs.getString("handleno");
				this.agenName =rs.getString("agenName");
				this.type =rs.getString("type");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加
	 */
	public void addArea() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_area(cityno,handleno,agenName,type) values('"
					+this.cityno+"','" + this.handleno + "','" + this.agenName + "','"+this.type+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void editArea() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_area set cityno='"+this.cityno+"',handleno='"+this.handleno+"',agenName='"+this.agenName+"',type='"+this.type+"' where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void delArea() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_area  where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示信息
	 * @return
	 */
	public Pager<Area> list(int page,int size,AreaForm areaForm,String keywords) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Area> areas=new Pager<Area>();
		areas.setPage(page);
		areas.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_area where 1=1";
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//模糊查询
			if(null != keywords && "" != keywords && !"".equals(keywords)){
				selectSql = selectSql+"   and (cityno like '%"+keywords+"%' or agenName like '%"+keywords+"%')";
			}
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 areas.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
				  int id=rs.getInt("id");
				  String cityno=rs.getString("cityno");
				  String handleno=rs.getString("handleno");
				  String agenName=rs.getString("agenName");
				  String type=rs.getString("type");
				  Area area=new Area(id,cityno,handleno,agenName,type);
				  areas.getDataSets().add(area);
			 }
			 areas.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return areas;

	}

	
	/**
	 * 
	 * @return
	 */
	public List<Area> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Area> areaList =new ArrayList<Area>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			String sql="select * from t_area where 1=1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				String cityno=rs.getString("cityno");
				String agenName =rs.getString("agenName");
				Area model=new Area(id, cityno, agenName, agenName, agenName);
				areaList.add(model);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("load Area list failed!", e);
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return areaList;
		
	}
	
	//定义无参构造函数
	public Area(){
		
	}
	public Area(Integer id, String cityno, String handleno, String agenName,
			String type) {
		super();
		this.id = id;
		this.cityno = cityno;
		this.handleno = handleno;
		this.agenName = agenName;
		this.type = type;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getCityno() {
		return cityno;
	}
	public void setCityno(String cityno) {
		this.cityno = cityno;
	}
	public String getHandleno() {
		return handleno;
	}
	public void setHandleno(String handleno) {
		this.handleno = handleno;
	}
	public String getAgenName() {
		return agenName;
	}
	public void setAgenName(String agenName) {
		this.agenName = agenName;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	
	
}
